Mega Code Archive
String Functions
1) ASCII(str)
2) BIN(N)
3) BIT_LENGTH(str)
4) CHAR() returns a string consisting of the characters given by the code values of those integers
5) CHAR_LENGTH(str),CHARACTER_LENGTH(str)
6) CONCAT() returns NULL if any argument is NULL
7) CONCAT(str1,str2, )
8) CONCAT_WS(separator,str1,str2, )
9) ELT(N,str1,str2,str3, )
10) EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
11) Expr LIKE pat [ESCAPE escape_char]
12) Expr REGEXP pat, expr RLIKE pat
13) Expr1 SOUNDS LIKE expr2
14) FIELD(str,str1,str2,str3, )
15) FIND_IN_SET(str,strlist)
16) FORMAT(12332 1,4)
17) FORMAT(12332 2,0)
18) FORMAT(X,D)
19) Get the 3 letters to the right of the first name
20) Get the first name in lower case
21) Get the ORD of the first name
22) Getting the LENGTH of the first name
23) HEX(abc)
24) HEX(N_or_S)
25) HEX(UNHEX(1267))
26) If you wanted to trim leading and trailing characters, you would use the keyword BOTH
27) In MySQL, LIKE is allowed on numeric expressions
28) INSERT(ABCDEFG, -1, 4, ZZZ)
29) INSERT(ABCDEFG, 3, 100, ZZZ)
30) INSERT(str,pos,len,newstr)
31) INSTR(str,substr)
32) LCASE(str)
33) LEFT(str,len)
34) LENGTH(str)
35) List the first name in upper case
36) LOAD_FILE(file_name)
37) LOCATE a letter in the first name
38) LOCATE(substr,str)
39) LOCATE(substr,str,pos)
40) LOWER(str)
41) LPAD(str,len,padstr)
42) LTRIM(str)
43) Make first name and last name as a set
44) MAKE_SET(0,a,b,c)
45) MAKE_SET(1 4,A,B,C)
46) MAKE_SET(1 4,A,B,NULL,C)
47) MAKE_SET(bits,str1,str2, )
48) ORD
49) Padding the to the right of the first name
50) Padding to the first name
51) QUOTE(NULL)
52) QUOTE(str)
53) Repeat first name for three times
54) REPEAT(str,count)
55) Replace letter J in the first name to letter G
56) REPLACE(str,from_str,to_str)
57) Returning the position of J in first name
58) Reverse the first name
59) REVERSE(str)
60) RIGHT(str,len)
61) RPAD(str,len,padstr)
62) RTRIM(str)
63) SELECT a REGEXP A, a REGEXP BINARY A
64) SELECT CONCAT(14 3)
65) SELECT ELT(-1, A, B, C, D)
66) SELECT ELT(10, A, B, C, D)
67) SELECT ELT(4, A, B, C, D)
68) SELECT EXPORT_SET(6,1,0,,,10)
69) SELECT HEX(CHAR(1,0)), HEX(CHAR(256))
70) SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256256))
71) SELECT TRIM(LEADING XXX FROM XXXFileName)
72) SOUNDEX(str)
73) SPACE(N)
74) Specifying the length and a starting position for the MID function
75) STRCMP(expr1,expr2)
76) String comparisons are not case sensitive unless one of the operands is a binary string
77) String Functions
78) String Operators
79) SUBSTRING(ABCDEFGHIJK FROM -4 FOR 2)
80) SUBSTRING(ABCDEFGHIJK FROM 4)
81) SUBSTRING(ABCDEFGHIJK, -3)
82) SUBSTRING(ABCDEFGHIJK, -5, 3)
83) SUBSTRING(ABCDEFGHIJK,5,6)
84) SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
85) SUBSTRING_INDEX(str,delim,count)
86) SUBSTRING_INDEX(www java2s com, , -2)
87) The TRIM() function can trim spaces and characters or groups of characters
88) To produce a string in a given character set, use the optional USING clause
89) To specify a different escape character, use the ESCAPE clause
90) To test for literal instances of a wildcard character, precede it by the escape character
91) TRIM([{BOTH LEADING TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
92) TRIM(BOTH x FROM xxxjava2sxxx)
93) TRIM(LEADING x FROM xxxhava2sxxx)
94) TRIM(TRAILING xyz FROM java2sxxyz)
95) Trimming the leading space in the first name
96) Trimming the space from right
97) UNHEX(GG)
98) UNHEX(HEX(string))
99) UNHEX(str) interprets each pair of hexadecimal digits as a number and converts it to the character
100) UPPER(str)
101) Use ASCII with the value in a table
102) Use BIT_LENGTH with the value in a table
103) Use Concat data from two different columns
104) Use Insert function to insert text to the first_name
105) Use LCASE to convert first name to lower case
106) Use LEFT with data in a table
107) Use replace function with data in a table
108) Use SOUNDEX with column data
109) Use space function to add space between first name and last name
110) Use SUBSTRING with data in a table
111) Use UCASE to convert first name to upper case
112) Using BIN with data in a table
113) Using CONCAT with table column
114) Using CONCAT_WS with data in a table
115) Using CONCAT_WS with NULL value
116) Using ELT with data in a table
117) Using FIND_IN_SET with data in a table
118) Using FORMAT with data in a table
119) Using STRCMP to compare the first_name